package br.usp.pcs.acs.negocios.dao;

import java.sql.Date;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import br.usp.pcs.acs.negocios.dao.interfaces.VOODaoInterface;
import br.usp.pcs.acs.negocios.dto.VOODto;


public class VOODao implements VOODaoInterface 
{

	@Override
	public void edit(VOODto inDto)
	{		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		
		try{
			conn = DatabaseConnect.getConnection();
			
			pstmt = conn.prepareStatement(CRUD_U);
			
			pstmt.setInt(9, inDto.getId());//id
			pstmt.setInt(1,inDto.getPortao_id());
			pstmt.setInt(2,inDto.getCa_id());
			pstmt.setByte(3,inDto.getAutorizado());
			pstmt.setDate(4, inDto.getDate());
			pstmt.setString(5,inDto.getOrigem());
			pstmt.setString(6,inDto.getDestino());
			pstmt.setInt(7,inDto.getNumPass());
			pstmt.setInt(8,inDto.getTempoVoo());
			
			pstmt.executeUpdate();
		}
		
		
		
		catch (SQLException ex){
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
		finally {
		    if (rs != null){
		    	try {rs.close();}catch (SQLException sqlEx) { } // ignore 
		    	rs = null;
		    }
		    if (pstmt != null){
		        try {pstmt.close();}catch (SQLException sqlEx) { } // ignore
		        pstmt = null;
		    }
		    if (conn != null){
		    	try{conn.close();}catch (SQLException sqlEx) { } // ignore
		    }
		}
		
	}

	@Override
	public ArrayList<VOODto> findAll() {
		return finding(CRUD_R);
	}
	private ArrayList<VOODto> finding(String busca) {
		ArrayList<VOODto> LoutDto = new ArrayList<VOODto>();
		VOODto tmpDto = null;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try{
			conn = DatabaseConnect.getConnection();			
			pstmt = conn.prepareStatement(busca);			
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{				
				tmpDto = new VOODto();
				tmpDto.setId(rs.getInt(1));
				tmpDto.setPortao_id(rs.getInt(2));
				tmpDto.setCa_id(rs.getInt(3));
				tmpDto.setAutorizado(rs.getByte(4));
				tmpDto.setDate(rs.getDate(5));
				tmpDto.setOrigem(rs.getString(6));
				tmpDto.setDestino(rs.getString(7));
				tmpDto.setNumPass(rs.getInt(8));
				tmpDto.setTempoVoo(rs.getInt(9));
				
				//System.out.println(tmpDto.getTipo());
				
				LoutDto.add(tmpDto);
			}
		}		
		catch (SQLException ex){
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
		finally {
		    if (rs != null){
		    	try {rs.close();}catch (SQLException sqlEx) { } // ignore 
		    	rs = null;
		    }
		    if (pstmt != null){
		        try {pstmt.close();}catch (SQLException sqlEx) { } // ignore
		        pstmt = null;
		    }
		    if (conn != null){
		    	try{conn.close();}catch (SQLException sqlEx) { } // ignore
		    }
		}
		
		return LoutDto;
	}

	@Override
	public ArrayList<VOODto> findAllByDestino(String Destino) {
		String busca =  "SELECT * FROM voo WHERE destino= \"" + Destino + "\"";
		return finding(busca);
	}

	@Override
	public ArrayList<VOODto> findAllByOrigem(String Origem) {
		String busca =  "SELECT * FROM voo WHERE origem= \"" + Origem + "\"";
		return finding(busca);
	}

	@Override
	public ArrayList<VOODto> findAllByOrigemAndDestino(String Origem,
			String Destino) {
		String busca =  "SELECT * FROM voo WHERE destino= \"" + Destino + "\" AND origem= \"" + Origem + "\"";
		return finding(busca);
	}

	@Override
	public ArrayList<VOODto> findAllByOrigemAndDestinoAndDate(String Origem,
			String Destino, Date date) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public VOODto findById(int id) {
		String busca =  "SELECT * FROM voo WHERE id = " + id;
		ArrayList<VOODto> ret= finding(busca);
		return ret.isEmpty()?null:ret.get(0);
	}

	@Override
	public ArrayList<VOODto> findNextNByDate(Date date, int n) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void insert(VOODto inDto)
	{		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		
		try{
			conn = DatabaseConnect.getConnection();
			
			pstmt = conn.prepareStatement(CRUD_C);
			
			pstmt.setNull(1, java.sql.Types.INTEGER);//id
			pstmt.setInt(2,inDto.getPortao_id());
			pstmt.setInt(3,inDto.getCa_id());
			pstmt.setByte(4,inDto.getAutorizado());
			pstmt.setDate(5, inDto.getDate());
			pstmt.setString(6,inDto.getOrigem());
			pstmt.setString(7,inDto.getDestino());
			pstmt.setInt(8,inDto.getNumPass());
			pstmt.setInt(9,inDto.getTempoVoo());
			
			pstmt.execute();
		}
		
		
		
		catch (SQLException ex){
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
		finally {
		    if (rs != null){
		    	try {rs.close();}catch (SQLException sqlEx) { } // ignore 
		    	rs = null;
		    }
		    if (pstmt != null){
		        try {pstmt.close();}catch (SQLException sqlEx) { } // ignore
		        pstmt = null;
		    }
		    if (conn != null){
		    	try{conn.close();}catch (SQLException sqlEx) { } // ignore
		    }
		}
		
	}

	@Override
	public void remove(int id) 
	{
		Connection conn = null;
		PreparedStatement pstmt = null;		
		try{
			conn = DatabaseConnect.getConnection();			
			pstmt = conn.prepareStatement(CRUD_D);			
			pstmt.setInt(1, id);			
			pstmt.execute();
		}		
		catch (SQLException ex){
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
		finally {
		    if (pstmt != null){
		        try {pstmt.close();}catch (SQLException sqlEx) { } // ignore
		        pstmt = null;
		    }
		    if (conn != null){
		    	try{conn.close();}catch (SQLException sqlEx) { } // ignore
		    }
		}
	}

	public ArrayList<VOODto> findByNumber(String vooNumber) {
		// TODO Auto-generated method stub M
		return null;
	}
	
	public ArrayList<VOODto> findPendentes() {
		// TODO M
		return null; 
	}

}
